﻿




-- 汇总在场人员信息
CREATE procedure [dbo].[GetCurrentInDataRy]
as
with tab as
(
select distinct jsdm,sfzh,jczt from Kqjl_Info_Copy where kqsj>=CONVERT(varchar(10), GETDATE(), 120) 
)

select yy.jsdm,isnull(cntjrrs,0) cntjrrs,isnull(cntcqrs,0) cntcqrs,isnull(cntjr,0) cntjr,isnull(cntcq,0) cntcq from
(
	select distinct jsdm from tab
) yy
left join
(
	select jsdm,count(1) cntjrrs from tab
	where jczt=1
	group by jsdm
) aa on (yy.jsdm=aa.jsdm)
left join
(
	select jsdm,count(1) cntcqrs from tab
	where jczt=0
	group by jsdm
) bb on (yy.jsdm=bb.jsdm)
left join
(
	select jsdm,count(1) cntjr from Kqjl_Info_Copy
	where jczt=1 and kqsj>=CONVERT(varchar(10), GETDATE(), 120) 
	group by jsdm
) cc on (yy.jsdm=cc.jsdm)
left join
(
	select jsdm,count(1) cntcq from Kqjl_Info_Copy
	where jczt=0 and kqsj>=CONVERT(varchar(10), GETDATE(), 120) 
	group by jsdm
) dd on (yy.jsdm=dd.jsdm)